# -*- coding: utf-8 -*-
# Part of Odoo. See LICENSE file for full copyright and licensing details.

from odoo import api, fields, models, _, tools


class MrpWorkorderSum(models.Model):
    _name = 'mrp.workorder.sum'
    _description = "Mrp Wrokorder Sum"
    _rec_name = 'product_id'
    _auto = False

    work_station_id = fields.Many2one('work.station', string='Work Station')
    work_station_code = fields.Char(related='work_station_id.code')
    work_station_name_eng = fields.Char(related='work_station_id.name_eng')
    production_id = fields.Many2one('mrp.production', 'WO')
    product_id = fields.Many2one('product.product', string='Product')

    # done_act_qty = fields.Float(string="Actual Processing Quantity")
    # 结存数量PCS
    qty_wip = fields.Float('WIP(PCS)', digits='Product Unit of Measure')
    qty_op_produced = fields.Float('Output(PCS)', digits='Product Unit of Measure')
    qty_op_reject = fields.Float('Scrap(PCS)', digits='Product Unit of Measure')
    # 结存数量PNL
    qty_wip_pnl = fields.Float('WIP(PNL)', digits='Product Unit of Measure')
    qty_op_produced_pnl = fields.Float('Output(PNL)', digits='Product Unit of Measure')
    qty_op_reject_pnl = fields.Float('Scrap(PNL)', digits='Product Unit of Measure')

    outbound_qty_wip_pnl = fields.Float('SUB(PNL)', digits='Product Unit of Measure')
    outbound_qty_wip_pnl_area = fields.Float(string='outbound area')

    qty_wip_set = fields.Float('WIP(SET)', digits='Product Unit of Measure')
    qty_op_produced_set = fields.Float('Output(SET)', digits='Product Unit of Measure')
    qty_op_reject_set = fields.Float('Scrap(SET)', digits='Product Unit of Measure')

    value_pnl = fields.Float(string='PCS/PNL')
    value_set = fields.Float(string='PCS/SET')

    # 结存毛面积㎡
    qty_wip_seta = fields.Float(string='WIP(M2)')
    qty_wip_seta_cell = fields.Float(string='AREA(PCS)')

    delivery_date = fields.Date(string='Delivery Date')
    pn = fields.Char(string='PN')

    pnl_size = fields.Char('PNL size')

    def action_wip_report_download(self):
        return {
            'type': 'ir.actions.act_url',
            'url': '/download/sb/production/wip/report/excel',
            'target': 'new',
        }

    def action_mrp_workorder_sum_report_download(self):
        return {
            'type': 'ir.actions.act_url',
            'url': '/download/mrp/workorder/wip/stocktaking/report/excel',
            'target': 'new',
        }

    @property
    def _table_query(self):
        return """
            select 
                row_number() OVER() AS id,
                ws.id as work_station_id,
                mp.id as production_id,
                wo.product_id as product_id,
                min(so.commitment_date) as delivery_date,
                sum(wo.qty_wip) as qty_wip,
                sum(wo.qty_op_produced) as qty_op_produced,
                sum(wo.qty_op_reject) as qty_op_reject,
                sum(wo.qty_wip_pnl) as qty_wip_pnl,
                sum(
                        case when wo.id in (select workorder_id from purchase_order_line where order_id is not null and workorder_id is not null) then wo.qty_wip_pnl
                        else 0 end
                ) as outbound_qty_wip_pnl,
                sum(
                        case when wo.id in (select workorder_id from purchase_order_line where order_id is not null and workorder_id is not null) then wo.qty_wip_pnl
                        else 0 end * puva.uom_value * puvb.uom_value / 1000000
                ) as outbound_qty_wip_pnl_area,
                sum(wo.qty_op_produced_pnl) as qty_op_produced_pnl,
                sum(wo.qty_op_reject_pnl) as qty_op_reject_pnl,
                sum(wo.qty_wip_set) as qty_wip_set,
                sum(wo.qty_op_produced_set) as qty_op_produced_set,
                sum(wo.qty_op_reject_set) as qty_op_reject_set,
                sum(wo.value_pnl) as value_pnl,
                sum(wo.value_set) as value_set,
                sum(wo.qty_wip_pnl * puva.uom_value * puvb.uom_value / 1000000) as qty_wip_seta,
                sum(puvc.uom_value * puvd.uom_value / 1000000) as qty_wip_seta_cell,
                min(so.pn) as pn,
                ROUND(puva.uom_value, 1)::text || 'X' || ROUND(puvb.uom_value, 1)::text pnl_size
            from work_station ws
            left join mrp_workcenter wc on wc.s_work_station_id = ws.id
            left join mrp_workorder wo on wc.id = wo.workcenter_id and wo.qty_wip_pnl > 0
            left join mrp_production mp on mp.id = wo.production_id
            left join sale_order so on so.name = mp.origin and so.delivery_status != 'full'
            left join product_product pp on pp.id = wo.product_id
            left join product_template pt on pt.id = pp.product_tmpl_id
            left join product_uom_value puva on puva.product_id = pt.id and puva.uom_id in (select id from uom_uom where uom_uom.code = 'PNLL')
            left join product_uom_value puvb on puvb.product_id = pt.id and puvb.uom_id in (select id from uom_uom where uom_uom.code = 'PNLW')
            left join product_uom_value puvc on puvc.product_id = pt.id and puvc.uom_id in (select id from uom_uom where uom_uom.code = 'PCSL')
            left join product_uom_value puvd on puvd.product_id = pt.id and puvd.uom_id in (select id from uom_uom where uom_uom.code = 'PCSW')
            where ws.active is true 
              and ws.disuse is not true 
             
            group by ws.id,mp.id,wo.product_id , puva.uom_value, puvb.uom_value
            order by mp.id,wo.product_id,ws.id
        """
    # and mp.mo_type != 'W'
